﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DMS.Base;
using DMS.BaseClass;
using DMS.Business.Modules.Organization;
using DMS.Business.Utility;
using DMS.Data;
using DMS.Data.Modules.ArchivesManagement;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace DMS.Business.Modules.ArchivesManagement
{
    public class ArchInfoService
    {
        public static AM_ArchivesInfo GetArchInfoById(string archInfoId)
        {
            using (var db = new DMSEntities())
            {
                return db.AM_ArchivesInfo.Where(p => p.ArchivesID == archInfoId).FirstOrDefault();
            }
        }

        /// <summary>
        /// 综合资料使用，用于通过基本ID获取资料信息
        /// </summary>
        /// <param name="baseInfoId"></param>
        /// <returns></returns>
        public static AM_ArchivesInfo GetArchInfoByBaseInfoId(string baseInfoId)
        {
            using (var db = new DMSEntities())
            {
                return db.AM_ArchivesInfo.Where(p => p.BaseInfoID == baseInfoId).FirstOrDefault();
            }
        }

        /// <summary>
        /// 查询基本信息
        /// </summary>
        /// <param name="actor">参与者，如果为空则不过滤权限</param>
        /// <param name="condition">条件</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页条数，如果小于等于0，则返回所有</param>
        /// <param name="total">总数</param>
        /// <returns></returns>
        public static List<AM_ArchivesInfo> GetArchInfos(Actor actor, string condition, int pageIndex, int pageSize,
                                                     out int total)
        {
            using (var db = new DMSEntities())
            {
                ////权限过滤
                //condition += GetLimitCondition(actor);

                int skinCount = (pageIndex - 1)*pageSize;

                var q = db.AM_ArchivesInfo
                    .Where("it.IsDeleted=false " + condition)
                    .OrderByDescending(p => p.BelongYear).ThenByDescending(p=>p.BelongMonth);

                total = q.Count();

                if (skinCount > total && pageSize > 0)
                {
                    skinCount = total - (total%pageSize);
                }

                List<AM_ArchivesInfo> list;
                if (pageSize > 0)
                    list = q.Skip(skinCount).Take(pageSize).ToList();
                else
                    list = q.ToList();

                return list;
            }
        }

        public static void SaveOrUpdateArchInfo(AM_ArchivesInfo archInfo)
        {
            using (var db = new DMSEntities())
            {
                var tArchInfo = db.AM_ArchivesInfo.FirstOrDefault(p => p.ArchivesID == archInfo.ArchivesID);
                if (tArchInfo != null) //更新
                {
                    db.ApplyCurrentValues("AM_ArchivesInfo", archInfo);
                }
                else //新增
                {
                    archInfo.IsDeleted = false;
                    db.AM_ArchivesInfo.AddObject(archInfo);
                }
                db.SaveChanges();
            }
        }

        public static void DeleteArchInfo(string archInfoId)
        {
            using (var db = new DMSEntities())
            {
                var archInfo = db.AM_ArchivesInfo.Where(p => p.ArchivesID == archInfoId).FirstOrDefault();
                if (archInfo != null)
                {
                    archInfo.IsDeleted = true;
                    db.SaveChanges();
                }
            }
        }

        public static void DestroyArchInfo(string condition)
        {
            using (var db = new DMSEntities())
            {
                var archInfos = db.AM_ArchivesInfo.Where(" 1=1 " + condition);

                foreach (var archInfo in archInfos)
                {
                    archInfo.IsDestroy = true;
                }

                db.SaveChanges();
            }
        }

        /// <summary>
        /// 处理上传的资料信息模板
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="baseInfoId"></param>
        /// <returns></returns>
        public static string ProcessArchInfosExcel(string filePath,string baseInfoId)
        {
            const string sheetName = "Sheet1";
            var ds = ClientHelper.Instance.ExcelToDS(filePath, sheetName);

            //异常信息
            string errorMessage = "";
            string succeedMessage = "ok";
            var archInfoList = new List<AM_ArchivesInfo>();

            //导入的是单个实体的资料信息
            if(!string.IsNullOrEmpty(baseInfoId))
            {
                archInfoList = ProcessSingleImport(ds, baseInfoId, out errorMessage);
            }else//整体导入，通过社保号查找
            {
                archInfoList = ProcessAllImport(ds, out errorMessage);
            }

            //校验无误，开始保存操作
            if (string.IsNullOrEmpty(errorMessage))
            {
                try
                {
                    using (var db = new DMSEntities())
                    {
                        //执行保存操作
                        foreach (var archInfo in archInfoList)
                        {
                            db.AM_ArchivesInfo.AddObject(archInfo);
                        }
                        db.SaveChanges();
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.LogException(ex);
                    errorMessage = ex.Message;
                }
            }

            return string.IsNullOrEmpty(errorMessage) ? succeedMessage: errorMessage;
        }

        /// <summary>
        /// 处理上传的资料信息模板
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="baseInfoId"></param>
        /// <returns></returns>
        public static string ProcessArchInfosExcelOld(string filePath,string baseInfoId)
        {
            const string sheetName = "Sheet1";
            var ds = ClientHelper.Instance.ExcelToDS(filePath, sheetName);

            //异常信息
            string errorMessage = "";
            string succeedMessage = "ok";
            var archInfoList = ProcessSingleImportOld(ds, baseInfoId, out errorMessage);

            //校验无误，开始保存操作
            if (string.IsNullOrEmpty(errorMessage))
            {
                try
                {
                    using (var db = new DMSEntities())
                    {
                        //执行保存操作
                        foreach (var archInfo in archInfoList)
                        {
                            db.AM_ArchivesInfo.AddObject(archInfo);
                        }
                        db.SaveChanges();
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.LogException(ex);
                    errorMessage = ex.Message;
                }
            }

            return string.IsNullOrEmpty(errorMessage) ? succeedMessage: errorMessage;
        }

        private static User currentUser = SystemContext.Current.LoginUser;

        public static List<AM_ArchivesInfo> ProcessSingleImport(DataSet ds ,string baseInfoId, out string errorMessage)
        {
            Department currentBigDept = currentUser.BigDept;
            var archInfoList = new List<AM_ArchivesInfo>();
            errorMessage = "";
            string currentTimeStr = DateTime.Now.ToString("yyyy-MM-dd");
            for (var i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                try
                {
                    var row = ds.Tables[0].Rows[i];

                    var items = row.ItemArray;

                    if (i == 0) //通过检查第一列是否为所属年份判断模版是否正确
                    {
                        int belongYearCheck;
                        bool templateCheck = Int32.TryParse(items[0].ToString().Replace("年",""), out belongYearCheck);
                        if (!templateCheck || belongYearCheck > 10000 || belongYearCheck < 1)
                        {
                            errorMessage = "模版有误，请使用正确的资料模版上传(资料信息导入模板-单个.xls)。";
                            break;
                        }
                    }

                    #region 必填字段检查

                    int requireCount = 4;

                    bool breakCurrentRecord = false;
                    for (int k = 0; k < requireCount; k++)
                    {
                        if (string.IsNullOrEmpty(Convert.ToString(items[k]).Trim()))
                        {

                            errorMessage += "第" + (i + 1)+ "行数据存在错误：必填字段不可为空，请核查。<br/>";
                            breakCurrentRecord = true;
                            break;
                        }
                    }
                    if (breakCurrentRecord) //不再进行当前记录检查
                    {
                        continue;
                    }
                    if (string.IsNullOrEmpty(Convert.ToString(items[7]).Trim()))
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：必填字段不可为空，请核查。<br/>";
                        continue;
                    }

                    #endregion

                    string Name = "";
                    string Type = "";
                    string BelongYear = "";
                    string BelongMonth = "";
                    string InitDate = "";
                    string CustodyYear = "";
                    string PersonName = "";
                    string PageCount = "";
                    string Remark = "";
                    string Supplement = "";

                    BelongYear = Convert.ToString(items[0]).Trim();
                    Name = Convert.ToString(items[1]).Trim();
                    Type = Convert.ToString(items[2]).Trim();
                    BelongMonth = Convert.ToString(items[3]).Trim();
                    InitDate = Convert.ToString(items[4]).Trim();
                    CustodyYear = Convert.ToString(items[5]).Replace("年","").Trim();
                    PersonName = Convert.ToString(items[6]).Trim();
                    PageCount = Convert.ToString(items[7]).Trim();
                    Remark = Convert.ToString(items[8]).Trim();
                    Supplement = Convert.ToString(items[9]).Trim();
                    
                    if (Name == "居民身份证" && string.IsNullOrEmpty(PersonName))
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误，居民身份证必需填写姓名，请核查。<br/>";
                        continue;
                    }

                    string[] typeArray = {"登记类", "征缴类", "综合类"};
                    //检查所属年份
                    int belongYearInt;
                    bool belongYearIntParseSucceed = Int32.TryParse(BelongYear, out belongYearInt);
                    if (!belongYearIntParseSucceed || belongYearInt > 10000 || belongYearInt < 1)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误，所属年份必须为合法的数字，请核查。<br/>";
                        continue;
                    }
                    if (!typeArray.Contains(Type))
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误，档案类型必须为登记类、征缴类、综合类中的一种，请核查。<br/>";
                        continue;
                    }
                    //检查所属月份
                    int belongMonthInt;
                    bool belongMonthIntParseSucceed = Int32.TryParse(BelongMonth, out belongMonthInt);
                    if (!belongMonthIntParseSucceed || belongMonthInt > 12 || belongMonthInt < 1)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误，所属月份必须为合法的数字，请核查。<br/>";
                        continue;
                    }
                    //检查日期
                    DateTime initDateParse;
                    bool initDateParseSucceed = DateTime.TryParse(InitDate, out initDateParse);
                    if (!initDateParseSucceed)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：档案形成日期格式有误（必须为“2011-12-03”格式），请核查。<br/>";
                        continue;
                    }
                    //检查保管年限
                    if(string.IsNullOrEmpty(CustodyYear))
                    {
                        CustodyYear = "0";
                    }
                    int custodyYearParse;
                    bool custodyYearParseSucceed = Int32.TryParse(CustodyYear, out custodyYearParse);
                    if (!custodyYearParseSucceed || custodyYearParse < 0)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：保管年限必须为0或正整数，请核查。<br/>";
                        continue;
                    }
                    //检查张数
                    int pageCountParse;
                    bool pageCountParseSucceed = Int32.TryParse(PageCount, out pageCountParse);
                    if (!pageCountParseSucceed || pageCountParse <= 0)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：张数必须为正整数，请核查。<br/>";
                        continue;
                    }
                    var archInfo = new AM_ArchivesInfo();
                    archInfo.ArchivesID = Guid.NewGuid().ToString();
                    archInfo.BaseInfoID = baseInfoId;
                    archInfo.IsDeleted = false;
                    archInfo.IsDestroy = false;

                    archInfo.Name = Name;
                    archInfo.Type = GetArchTypeCode(Type);
                    archInfo.BelongYear = BelongYear;
                    archInfo.BelongMonth = BelongMonth;
                    archInfo.InitDate = initDateParse;
                    archInfo.CustodyBeginDate = new DateTime(initDateParse.Year + 1, 1, 1);
                    archInfo.CustodyYear = custodyYearParse;
                    if(custodyYearParse!=0)
                        archInfo.CustodyEndDate = new DateTime(archInfo.CustodyBeginDate.Value.Year + custodyYearParse,
                                                               1, 1);
                    archInfo.PersonName = PersonName;
                    archInfo.PageCount = pageCountParse;
                    archInfo.InputPersonID = currentUser.ID;
                    archInfo.InputPersonName = currentUser.Name;
                    archInfo.DeptName = currentBigDept.Name;
                    archInfo.DeptID = currentBigDept.ID;
                    archInfo.InputDate = currentTimeStr;
                    archInfo.Remark = Remark;
                    archInfo.Supplement = Supplement;

                    archInfoList.Add(archInfo);
                }
                catch (Exception ex)
                {
                    errorMessage += "第" + (i + 1)+ "行数据存在错误，错误信息：" + ex.Message + "<br/>";
                }
            }
            return archInfoList;
        }

        public static List<AM_ArchivesInfo> ProcessSingleImportOld(DataSet ds ,string baseInfoId, out string errorMessage)
        {
            Department currentBigDept = currentUser.BigDept;
            var archInfoList = new List<AM_ArchivesInfo>();
            errorMessage = "";
            string currentTimeStr = DateTime.Now.ToString("yyyy-MM-dd");
            for (var i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                try
                {
                    var row = ds.Tables[0].Rows[i];

                    var items = row.ItemArray;

                    if (i == 0) //通过检查第一列是否为所属年份判断模版是否正确
                    {
                        int belongYearCheck;
                        bool templateCheck = Int32.TryParse(items[0].ToString().Replace("年",""), out belongYearCheck);
                        if (!templateCheck || belongYearCheck > 10000 || belongYearCheck < 1)
                        {
                            errorMessage = "模版有误，请使用正确的资料模版上传(资料信息导入模板-单个.xls)。";
                            break;
                        }
                    }

                    #region 必填字段检查

                    int requireCount = 4;

                    bool breakCurrentRecord = false;
                    for (int k = 0; k < requireCount; k++)
                    {
                        if (string.IsNullOrEmpty(Convert.ToString(items[k]).Trim()))
                        {

                            errorMessage += "第" + (i + 1)+ "行数据存在错误：必填字段不可为空，请核查。<br/>";
                            breakCurrentRecord = true;
                            break;
                        }
                    }
                    if (breakCurrentRecord) //不再进行当前记录检查
                    {
                        continue;
                    }
                    if (string.IsNullOrEmpty(Convert.ToString(items[9]).Trim()))
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：必填字段不可为空，请核查。<br/>";
                        continue;
                    }

                    #endregion

                    string Name = "";
                    string Type = "";
                    string BelongYear = "";
                    string BelongMonth = "";
                    string InitDate = "";
                    string CustodyYear = "";
                    string PersonName = "";
                    string PageCount = "";
                    string Remark = "";
                    string Supplement = "";

                    BelongYear = Convert.ToString(items[0]).Trim();
                    Name = Convert.ToString(items[1]).Trim();
                    Type = Convert.ToString(items[2]).Trim();
                    BelongMonth = Convert.ToString(items[3]).Trim();
                    InitDate = Convert.ToString(items[4]).Trim();
                    CustodyYear = Convert.ToString(items[6]).Replace("年","").Trim();
                    PersonName = Convert.ToString(items[8]).Trim();
                    PageCount = Convert.ToString(items[9]).Trim();
                    Remark = Convert.ToString(items[12]).Trim();
                    Supplement = Convert.ToString(items[13]).Trim();
                    
                    if (Name == "居民身份证" && string.IsNullOrEmpty(PersonName))
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误，居民身份证必需填写姓名，请核查。<br/>";
                        continue;
                    }

                    string[] typeArray = {"登记类", "征缴类", "综合类"};
                    //检查所属年份
                    int belongYearInt;
                    bool belongYearIntParseSucceed = Int32.TryParse(BelongYear, out belongYearInt);
                    if (!belongYearIntParseSucceed || belongYearInt > 10000 || belongYearInt < 1)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误，所属年份必须为合法的数字，请核查。<br/>";
                        continue;
                    }
                    if (!typeArray.Contains(Type))
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误，档案类型必须为登记类、征缴类、综合类中的一种，请核查。<br/>";
                        continue;
                    }
                    //检查所属月份
                    int belongMonthInt;
                    bool belongMonthIntParseSucceed = Int32.TryParse(BelongMonth, out belongMonthInt);
                    if (!belongMonthIntParseSucceed || belongMonthInt > 12 || belongMonthInt < 1)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误，所属月份必须为合法的数字，请核查。<br/>";
                        continue;
                    }
                    //检查日期
                    DateTime initDateParse;
                    bool initDateParseSucceed = DateTime.TryParse(InitDate, out initDateParse);
                    if (!initDateParseSucceed)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：档案形成日期格式有误（必须为“2011-12-03”格式），请核查。<br/>";
                        continue;
                    }
                    //检查保管年限
                    if(string.IsNullOrEmpty(CustodyYear))
                    {
                        CustodyYear = "0";
                    }
                    int custodyYearParse;
                    bool custodyYearParseSucceed = Int32.TryParse(CustodyYear, out custodyYearParse);
                    if (!custodyYearParseSucceed || custodyYearParse < 0)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：保管年限必须为0或正整数，请核查。<br/>";
                        continue;
                    }
                    //检查张数
                    int pageCountParse;
                    bool pageCountParseSucceed = Int32.TryParse(PageCount, out pageCountParse);
                    if (!pageCountParseSucceed || pageCountParse <= 0)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：张数必须为正整数，请核查。<br/>";
                        continue;
                    }
                    var archInfo = new AM_ArchivesInfo();
                    archInfo.ArchivesID = Guid.NewGuid().ToString();
                    archInfo.BaseInfoID = baseInfoId;
                    archInfo.IsDeleted = false;
                    archInfo.IsDestroy = false;

                    archInfo.Name = Name;
                    archInfo.Type = GetArchTypeCode(Type);
                    archInfo.BelongYear = BelongYear;
                    archInfo.BelongMonth = BelongMonth;
                    archInfo.InitDate = initDateParse;
                    archInfo.CustodyBeginDate = new DateTime(initDateParse.Year + 1, 1, 1);
                    archInfo.CustodyYear = custodyYearParse;
                    if(custodyYearParse!=0)
                        archInfo.CustodyEndDate = new DateTime(archInfo.CustodyBeginDate.Value.Year + custodyYearParse,
                                                               1, 1);
                    archInfo.PersonName = PersonName;
                    archInfo.PageCount = pageCountParse;
                    archInfo.InputPersonID = currentUser.ID;
                    archInfo.InputPersonName = currentUser.Name;
                    archInfo.DeptName = currentBigDept.Name;
                    archInfo.DeptID = currentBigDept.ID;
                    archInfo.InputDate = currentTimeStr;
                    archInfo.Remark = Remark;
                    archInfo.Supplement = Supplement;

                    archInfoList.Add(archInfo);
                }
                catch (Exception ex)
                {
                    errorMessage += "第" + (i + 1)+ "行数据存在错误，错误信息：" + ex.Message + "<br/>";
                }
            }
            return archInfoList;
        }

        public static List<AM_ArchivesInfo> ProcessAllImport(DataSet ds , out string errorMessage)
        {
            var archInfoList = new List<AM_ArchivesInfo>();
            Department currentBigDept = currentUser.BigDept;
            errorMessage = "";
            string currentTimeStr = DateTime.Now.ToString("yyyy-MM-dd");
            for (var i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                try
                {
                    var row = ds.Tables[0].Rows[i];

                    var items = row.ItemArray;

                    //if (i == 0) //通过表头检查模版是否正确
                    //{
                    //    if (items[0].ToString() != "*社保号")
                    //    {
                    //        errorMessage = "模版有误，请使用正确的资料模版上传(资料信息导入模板-全局.xls)。";
                    //        break;
                    //    }
                    //    continue;
                    //}
                    var baseInfo = BaseInfoService.GetBaseInfoByInsuranceCode(Convert.ToString(items[0]).Trim());
                    if(baseInfo==null)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：该社保号找不到对应的基本信息，请核查数据是否正确或是否使用了正确的模板。<br/>";
                        continue;
                    }


                    #region 必填字段检查

                    int requireCount = 5;

                    bool breakCurrentRecord = false;
                    for (int k = 0; k < requireCount; k++)
                    {
                        if (string.IsNullOrEmpty(Convert.ToString(items[k]).Trim()))
                        {

                            errorMessage += "第" + (i + 1)+ "行数据存在错误：必填字段不可为空，请核查。<br/>";
                            breakCurrentRecord = true;
                            break;
                        }
                    }
                    if (breakCurrentRecord) //不再进行当前记录检查
                    {
                        continue;
                    }
                    if (string.IsNullOrEmpty(Convert.ToString(items[8]).Trim()))
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：必填字段不可为空，请核查。<br/>";
                        continue;
                    }

                    #endregion

                    string InsuranceCode = "";
                    string Name = "";
                    string Type = "";
                    string BelongYear = "";
                    string BelongMonth = "";
                    string InitDate = "";
                    string CustodyYear = "";
                    string PersonName = "";
                    string PageCount = "";
                    string Remark = "";
                    string Supplement = "";

                    InsuranceCode=Convert.ToString(items[0]).Trim();
                    BelongYear = Convert.ToString(items[1]).Trim().Replace("年","");
                    Name = Convert.ToString(items[2]).Trim();
                    Type = Convert.ToString(items[3]).Trim();
                    BelongMonth = Convert.ToString(items[4]).Trim();
                    InitDate = Convert.ToString(items[5]).Trim();
                    CustodyYear = Convert.ToString(items[6]).Trim();
                    PersonName = Convert.ToString(items[7]).Trim();
                    PageCount = Convert.ToString(items[8]).Trim();
                    Remark = Convert.ToString(items[9]).Trim();
                    Supplement = Convert.ToString(items[10]).Trim();
                    
                    if (Name == "居民身份证" && string.IsNullOrEmpty(PersonName))
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误，居民身份证必需填写姓名，请核查。<br/>";
                        continue;
                    }

                    string[] typeArray = {"登记类", "征缴类", "综合类"};
                    //检查所属年份
                    int belongYearInt;
                    bool belongYearIntParseSucceed = Int32.TryParse(BelongYear, out belongYearInt);
                    if (!belongYearIntParseSucceed || belongYearInt > 10000 || belongYearInt < 1)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误，所属年份必须为合法的数字，请核查。<br/>";
                        continue;
                    }
                    if (!typeArray.Contains(Type))
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误，档案类型必须为登记类、征缴类、综合类中的一种，请核查。<br/>";
                        continue;
                    }
                    //检查所属月份
                    int belongMonthInt;
                    bool belongMonthIntParseSucceed = Int32.TryParse(BelongMonth, out belongMonthInt);
                    if (!belongMonthIntParseSucceed || belongMonthInt > 12 || belongMonthInt < 1)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误，所属月份必须为合法的数字，请核查。<br/>";
                        continue;
                    }
                    //检查日期
                    DateTime initDateParse;
                    bool initDateParseSucceed = DateTime.TryParse(InitDate, out initDateParse);
                    if (!initDateParseSucceed)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：档案形成日期格式有误（必须为“2011-12-03”格式），请核查。<br/>";
                        continue;
                    }
                    //检查保管年限
                    if(string.IsNullOrEmpty(CustodyYear))
                    {
                        CustodyYear = "0";
                    }
                    int custodyYearParse;
                    bool custodyYearParseSucceed = Int32.TryParse(CustodyYear, out custodyYearParse);
                    if (!custodyYearParseSucceed || custodyYearParse < 0)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：保管年限必须为0或正整数，请核查。<br/>";
                        continue;
                    }
                    //检查张数
                    int pageCountParse;
                    bool pageCountParseSucceed = Int32.TryParse(PageCount, out pageCountParse);
                    if (!pageCountParseSucceed || pageCountParse <= 0)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：张数必须为正整数，请核查。<br/>";
                        continue;
                    }

                    var archInfo = new AM_ArchivesInfo();
                    archInfo.ArchivesID = Guid.NewGuid().ToString();
                    archInfo.BaseInfoID = baseInfo.BaseInfoID;
                    archInfo.IsDeleted = false;
                    archInfo.IsDestroy = false;

                    archInfo.Name = Name;
                    archInfo.Type = GetArchTypeCode(Type);;
                    archInfo.BelongYear = BelongYear;
                    archInfo.BelongMonth = BelongMonth;
                    archInfo.InitDate = initDateParse;
                    archInfo.CustodyBeginDate = new DateTime(initDateParse.Year + 1, 1, 1);
                    archInfo.CustodyYear = custodyYearParse;
                    if(custodyYearParse!=0)
                        archInfo.CustodyEndDate = new DateTime(archInfo.CustodyBeginDate.Value.Year + custodyYearParse,
                                                               1, 1);
                    archInfo.PersonName = PersonName;
                    archInfo.PageCount = pageCountParse;
                    archInfo.InputPersonID = currentUser.ID;
                    archInfo.InputPersonName = currentUser.Name;
                    archInfo.DeptName = currentBigDept.Name;
                    archInfo.DeptID = currentBigDept.ID;
                    archInfo.InputDate = currentTimeStr;
                    archInfo.Remark = Remark;
                    archInfo.Supplement = Supplement;

                    archInfoList.Add(archInfo);
                }
                catch (Exception ex)
                {
                    errorMessage += "第" + (i + 1)+ "行数据存在错误，错误信息：" + ex.Message + "<br/>";
                }
            }
            return archInfoList;
        }

        private static string GetArchTypeCode(string typeName)
        {
            string typeCode = "";
            switch (typeName)
            {
                case "登记类":
                    typeCode = "DJ";
                    break;
                case "征缴类":
                    typeCode = "ZJ";
                    break;
                case "综合类":
                    typeCode = "ZH";
                    break;
            }
            return typeCode;
        }

        /// <summary>
        /// 处理上传的资料信息模板
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static string ProcessZHArchInfosExcel(string filePath)
        {
            const string sheetName = "Sheet1";
            var ds = ClientHelper.Instance.ExcelToDS(filePath, sheetName);

            //异常信息
            string errorMessage = "";
            string succeedMessage = "ok";
            var baseInfoList = new List<AM_BaseInfo>();
            var archInfoList = new List<AM_ArchivesInfo>();
            Department currentDept = currentUser.BigDept;
            string currentTimeStr = DateTime.Now.ToString("yyyy-MM-dd");
            for (var i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                try
                {
                    var row = ds.Tables[0].Rows[i];

                    var items = row.ItemArray;

                    #region 必填字段检查

                    int requireCount = 4;

                    bool breakCurrentRecord = false;
                    for (int k = 0; k < requireCount; k++)
                    {
                        if (string.IsNullOrEmpty(Convert.ToString(items[k]).Trim()))
                        {

                            errorMessage += "第" + (i + 1)+ "行数据存在错误：必填字段不可为空，请核查。<br/>";
                            breakCurrentRecord = true;
                            break;
                        }
                    }
                    if (breakCurrentRecord) //不再进行当前记录检查
                    {
                        continue;
                    }
                    if (string.IsNullOrEmpty(Convert.ToString(items[6]).Trim()))
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：必填字段不可为空，请核查。<br/>";
                        continue;
                    }

                    #endregion

                    string BoxCode = "";
                    string BoxPosition = "";
                    string Name = "";
                    string BelongYear = "";
                    string InitDate = "";
                    string CustodyYear = "";
                    string PageCount = "";
                    string Remark = "";
                    string Supplement = "";
                    BoxCode= Convert.ToString(items[0]).Trim();
                    BoxPosition = Convert.ToString(items[1]).Trim();
                    Name = Convert.ToString(items[2]).Trim();
                    BelongYear = Convert.ToString(items[3]).Trim().Replace("年","");
                    InitDate = Convert.ToString(items[4]).Trim();
                    CustodyYear = Convert.ToString(items[5]).Trim();
                    PageCount = Convert.ToString(items[6]).Trim();
                    Remark = Convert.ToString(items[7]).Trim();
                    Supplement = Convert.ToString(items[8]).Trim();

                    //检查所属年份
                    int belongYearInt;
                    bool belongYearIntParseSucceed = Int32.TryParse(BelongYear, out belongYearInt);
                    if (!belongYearIntParseSucceed || belongYearInt > 10000 || belongYearInt < 1)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误，所属年份必须为合法的数字，请核查。<br/>";
                        continue;
                    }
                    //检查日期
                    DateTime initDateParse;
                    bool initDateParseSucceed = DateTime.TryParse(InitDate, out initDateParse);
                    if (!initDateParseSucceed)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：档案形成日期格式有误（必须为“2011-12-03”格式），请核查。<br/>";
                        continue;
                    }
                    //检查保管年限
                    if(string.IsNullOrEmpty(CustodyYear))
                    {
                        CustodyYear = "0";
                    }
                    int custodyYearParse;
                    bool custodyYearParseSucceed = Int32.TryParse(CustodyYear, out custodyYearParse);
                    if (!custodyYearParseSucceed || custodyYearParse < 0)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：保管年限必须为0或正整数，请核查。<br/>";
                        continue;
                    }
                    //检查张数
                    int pageCountParse;
                    bool pageCountParseSucceed = Int32.TryParse(PageCount, out pageCountParse);
                    if (!pageCountParseSucceed || pageCountParse <= 0)
                    {
                        errorMessage += "第" + (i + 1)+ "行数据存在错误：张数必须为正整数，请核查。<br/>";
                        continue;
                    }

                    var baseInfo = new AM_BaseInfo();
                    baseInfo.BaseInfoID = Guid.NewGuid().ToString();
                    baseInfo.Type = "ZH";
                    baseInfo.BoxCode = BoxCode;
                    baseInfo.BoxPosition = BoxPosition;
                    baseInfo.IsDeleted = false;
                    baseInfo.Remark = Remark;
                    baseInfo.DeptID = currentDept.ID;
                    baseInfo.DeptName = currentDept.Name;

                    var archInfo = new AM_ArchivesInfo();
                    archInfo.ArchivesID = Guid.NewGuid().ToString();
                    archInfo.BaseInfoID = baseInfo.BaseInfoID;
                    archInfo.IsDeleted = false;
                    archInfo.IsDestroy = false;

                    archInfo.Name = Name;
                    archInfo.BelongYear = BelongYear;
                    archInfo.InitDate = initDateParse;
                    archInfo.CustodyBeginDate = new DateTime(initDateParse.Year + 1, 1, 1);
                    archInfo.CustodyYear = custodyYearParse;
                    if(custodyYearParse!=0)
                        archInfo.CustodyEndDate = new DateTime(archInfo.CustodyBeginDate.Value.Year + custodyYearParse,
                                                               1, 1);
                    archInfo.PageCount = pageCountParse;
                    archInfo.InputPersonID = currentUser.ID;
                    archInfo.InputPersonName = currentUser.Name;
                    archInfo.DeptID = currentDept.ID;
                    archInfo.DeptName = currentDept.Name;
                    archInfo.InputDate = currentTimeStr;
                    archInfo.Remark = Remark;
                    archInfo.Supplement = Supplement;

                    baseInfoList.Add(baseInfo);
                    archInfoList.Add(archInfo);

                }catch (Exception ex)
                {
                    errorMessage += "第" + (i + 1)+ "行数据存在错误，错误信息：" + ex.Message + "<br/>";
                }
            }

            //校验无误，开始保存操作
            if (string.IsNullOrEmpty(errorMessage))
            {
                try
                {
                    using (var db = new DMSEntities())
                    {
                        //执行保存操作
                        foreach (var baseInfo in baseInfoList)
                        {
                            db.AM_BaseInfo.AddObject(baseInfo);
                        }
                        db.SaveChanges();
                        foreach (var archivesInfo in archInfoList)
                        {
                            db.AM_ArchivesInfo.AddObject(archivesInfo);
                        }
                        db.SaveChanges();
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.LogException(ex);
                    errorMessage = ex.Message;
                }
            }

            return string.IsNullOrEmpty(errorMessage) ? succeedMessage: errorMessage;
        }

        public static List<DataTable> GetArchInfoTable(Actor actor, string condition)
        {
            int total;
            var list = GetArchInfos(actor, condition, 0, -1, out total);

            if (list.Count > 65535)
                return null;

            List<DataTable> tables = new List<DataTable>();

            #region 表头初始化

            DataTable dt = new DataTable();
            dt.TableName = "档案资料信息";
            dt.Columns.Add("档案盒编号");
            dt.Columns.Add("档案盒所在位置");
            dt.Columns.Add("社保号");
            dt.Columns.Add("单位名称");     
            dt.Columns.Add("档案名称");
            dt.Columns.Add("所属年度");
            dt.Columns.Add("所属月份");
            dt.Columns.Add("档案类型");
            dt.Columns.Add("档案形成日期");
            dt.Columns.Add("保管起始日期");
            dt.Columns.Add("档案保管年限");
            dt.Columns.Add("到期日期");
            dt.Columns.Add("姓名");
            dt.Columns.Add("张数");
            dt.Columns.Add("是否销毁");
            dt.Columns.Add("备注");
            dt.Columns.Add("补充说明");
            dt.Columns.Add("档案录入人员");
            dt.Columns.Add("档案录入日期");

            DataRow dr1 = dt.NewRow();

            dr1["档案盒编号"] = "档案盒编号";
            dr1["档案盒所在位置"] = "档案盒所在位置";
            dr1["社保号"] = "社保号";
            dr1["单位名称"] = "单位名称/缴费人姓名";
            dr1["档案名称"] = "档案名称";
            dr1["所属年度"] = "所属年度";
            dr1["所属月份"] = "所属月份";
            dr1["档案类型"] = "档案类型";
            dr1["档案形成日期"] = "档案形成日期";
            dr1["保管起始日期"] = "保管起始日期";
            dr1["档案保管年限"] = "档案保管年限";
            dr1["到期日期"] = "到期日期";
            dr1["姓名"] = "姓名";
            dr1["张数"] = "张数";
            dr1["是否销毁"] = "是否销毁";
            dr1["备注"] = "备注";
            dr1["补充说明"] = "补充说明";
            dr1["档案录入人员"] = "档案录入人员";
            dr1["档案录入日期"] = "档案录入日期";
            dt.Rows.Add(dr1);

            #endregion

            foreach (var exportRow in list)
            {
                DataRow dr = dt.NewRow();

                AM_BaseInfo baseInfo = BaseInfoService.GetBaseInfoById(exportRow.BaseInfoID);
                dr["档案盒编号"] = baseInfo.BoxCode;
                dr["档案盒所在位置"] = baseInfo.BoxPosition;
                dr["社保号"] = baseInfo.InsuranceCode;
                dr["单位名称"] = baseInfo.Type == "GR" ? baseInfo.PayerName : baseInfo.UnitName;

                dr["档案名称"] = exportRow.Name;
                dr["所属年度"] = exportRow.BelongYear;
                dr["所属月份"] = exportRow.BelongMonth;
                switch (exportRow.Type)
                {
                    case "DJ":
                        dr["档案类型"] = "登记类";
                        break;
                    case "ZJ":
                        dr["档案类型"] = "征缴类";
                        break;
                    case "ZH":
                        dr["档案类型"] = "综合类";
                        break;
                    default:
                        dr["档案类型"] = "未指定";
                        break;
                }
                dr["档案形成日期"] = exportRow.InitDate.HasValue?exportRow.InitDate.Value.ToShortDateString():"";
                dr["保管起始日期"] = exportRow.CustodyBeginDate.HasValue?exportRow.CustodyBeginDate.Value.ToShortDateString():"";
                dr["档案保管年限"] = (exportRow.CustodyYear.HasValue && exportRow.CustodyYear.Value == 0)
                                   ? "永久"
                                   : (exportRow.CustodyYear??0).ToString();
                dr["到期日期"] = exportRow.CustodyEndDate.HasValue?exportRow.CustodyEndDate.Value.ToShortDateString():"";
                dr["姓名"] = exportRow.PersonName;
                dr["张数"] = exportRow.PageCount;
                dr["是否销毁"] = exportRow.IsDestroy??false?"是":"否";
                dr["备注"] = exportRow.Remark;
                dr["补充说明"] = exportRow.Supplement;
                dr["档案录入人员"] = exportRow.InputPersonName;
                dr["档案录入日期"] = exportRow.InputDate;

                dt.Rows.Add(dr);
            }
            tables.Add(dt);
            return tables;
        }
    }
}
